/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.google.code.netbuilder.dal.providers.jdbc;

import com.google.code.netbuilder.dal.entities.Cable;
import com.google.code.netbuilder.dal.entities.Wire;
import com.google.code.netbuilder.dal.providers.ICableProvider;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Юрийэ
 */
public class CableProviderJDBC implements ICableProvider{

    @Override
    public void save(Cable entity) throws RuntimeException {
        
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        
        try{
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            
            String queryInsertCable = "INSERT INTO cable(name, creation_date, "
                    + "modification_date, object_state, type) VALUES ( ?, ?, ?, ?, ?);";
            pst1 = conn.prepareStatement(queryInsertCable, PreparedStatement.RETURN_GENERATED_KEYS);
            pst1.setString(1, entity.getName());
            pst1.setDate(2, new Date(entity.getCreationDate().getTime()));
            pst1.setDate(3, new Date(entity.getModificationDate().getTime()));
            pst1.setInt(4, entity.getObjectState());
            pst1.setString(5, entity.getType());
            
            pst1.executeUpdate();
            
            rs1 = pst1.getGeneratedKeys();
            rs1.next();
            long f = rs1.getLong(1);
            entity.setId(f);
            
            List<Wire> wires = entity.getCableWires();
            
            String queryInsertWires = "Insert into wire(name, creation_date, "
                    + "modification_date, object_state, from_port_id, to_port_id, cable_id) "
                    + "Values(?, ?, ?, ?, ?, ?, "+ entity.getId()+")";
            
            pst2 = conn.prepareStatement(queryInsertWires, PreparedStatement.RETURN_GENERATED_KEYS);
            
            for(int i = 0; i< wires.size(); i++){
                Wire wire = wires.get(i);
                pst2.setString(1, wire.getName());
                pst2.setDate(2, new Date(wire.getCreationDate().getTime()));
                pst2.setDate(3, new Date(wire.getModificationDate().getTime()));
                pst2.setInt(4, wire.getObjectState());
                if (wire.getFromPort() != null) 
                    pst2.setLong(5, wire.getFromPort().getId());
                else pst2.setNull(5, java.sql.Types.BIGINT);                
                if ( wire.getToPort()!= null) pst2.setLong(6, wire.getToPort().getId());
                else   pst2.setNull(6, java.sql.Types.BIGINT);  
               
                pst2.executeUpdate();
                rs2 = pst2.getGeneratedKeys();
                rs2.next();
                wire.setId(rs2.getLong(1));
                rs2.close();
            }
            conn.commit();
        } catch (Exception e)
        {
            Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            try {
                if (conn!= null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new RuntimeException(e);
        }
        finally {
            try {
                if (rs1!= null) rs1.close();
                if (rs2!= null) rs2.close();
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }        
        }
    }

    @Override
    public void update(Cable entity) throws RuntimeException {
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
       
        try{
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            
            String queryUpdateCable = "Update cable set name=?, creation_date=?, "
                    + "modification_date=?, object_state=?, type=? where id=? ;";
            pst1 = conn.prepareStatement(queryUpdateCable);
            pst1.setString(1, entity.getName());
            pst1.setDate(2, new Date(entity.getCreationDate().getTime()));
            pst1.setDate(3, new Date(entity.getModificationDate().getTime()));
            pst1.setInt(4, entity.getObjectState());
            pst1.setString(5, entity.getType());
            pst1.setLong(6, entity.getId());
            
            pst1.executeUpdate();
            
            List<Wire> wires = entity.getCableWires();
            
            String queryInsertWires = "Update wire set name=?, creation_date=?, "
                    + "modification_date=?, object_state=?, from_port_id=?, to_port_id=? where cable_id="
                    + entity.getId()+";";
            
            pst2 = conn.prepareStatement(queryInsertWires);
            
            for(int i = 0; i< wires.size(); i++){
                Wire wire = wires.get(i);
                pst2.setString(1, wire.getName());
                pst2.setDate(2, new Date(wire.getCreationDate().getTime()));
                pst2.setDate(3, new Date(wire.getModificationDate().getTime()));
                pst2.setInt(4, wire.getObjectState());
                if (wire.getFromPort() != null) 
                    pst2.setLong(5, wire.getFromPort().getId());
                else pst2.setNull(5, java.sql.Types.BIGINT);                
                if ( wire.getToPort()!= null) pst2.setLong(6, wire.getToPort().getId());
                else   pst2.setNull(6, java.sql.Types.BIGINT);
                pst2.executeUpdate();               
            }
            conn.commit();
        } catch (Exception e)
        {
            Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            try {
                if (conn!= null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new RuntimeException(e);
        }
        finally {
            try {
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }        
        }
    }

    @Override
    public void delete(Cable entity) throws RuntimeException {
        
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        
        try{
            
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            
            
            String queryDeleteWireVersion = "Delete from wire_version where wire_id=?;";
            String queryDeleteWires = "Delete from wire where id=?;";
            pst1 = conn.prepareStatement(queryDeleteWireVersion);
            pst2 = conn.prepareStatement(queryDeleteWires);            
            List<Wire> wires = entity.getCableWires();
            
            for(int i =0; i< wires.size(); i++){
                pst1.setLong(1, wires.get(i).getId());
                pst2.setLong(1, wires.get(i).getId());
                pst1.executeUpdate();
                pst2.executeUpdate();
            }
                        
            String queryDeleteCable = "Delete from cable where id="+entity.getId()+";";
            pst3 = conn.prepareStatement(queryDeleteCable);
            pst3.executeUpdate();
            
            conn.commit();
        } catch(Exception e){
            Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            try {
                if (conn!= null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new RuntimeException(e);
        }
        finally{
            try {
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (pst3 != null) pst3.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }
        }
    }

    @Override
    public List<Cable> findAll() throws RuntimeException {
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        List<Cable> cables = new ArrayList<Cable>();  
        try{
            conn = JDBCServise.getConnection();
            
            pst1 = conn.prepareStatement("Select * from cable;");
            rs1 = pst1.executeQuery();
            
            while(rs1.next()){
                Cable cable = new Cable();
                cable.setId(rs1.getLong("id"));
                cable.setName(rs1.getString("name"));
                cable.setCreationDate(rs1.getDate("creation_date"));
                cable.setModificationDate(rs1.getDate("modification_date"));
                cable.setObjectState(rs1.getInt("object_state"));
                cable.setType(rs1.getString("type"));

                List<Wire> wires = new ArrayList<Wire>();
                pst2 = conn.prepareStatement("Select * from wire where cable_id="+cable.getId()+";");
                rs2 = pst2.executeQuery();
                while(rs2.next()){
                    Wire wire = new Wire();
                    wire.setId(rs2.getLong("id"));
                    wire.setName(rs2.getString("name"));
                    wire.setCreationDate(rs2.getDate("creation_date"));
                    wire.setModificationDate(rs2.getDate("modification_date"));
                    wire.setObjectState(rs2.getInt("object_state"));
                    wires.add(wire);
                }
                cables.add(cable);
            }
                       
        } catch(Exception e){
            Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            throw new RuntimeException(e);
        }
        finally {
            try {
                if (rs1!= null) rs1.close();
                if (rs2!= null) rs2.close();
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (conn != null) conn.close();
                
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }        
        }
        return cables;
    }

    @Override
    public Cable findByID(Integer id) throws RuntimeException {
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        Cable cable = new Cable();
        
        try{
            conn = JDBCServise.getConnection();
            
            pst1 = conn.prepareStatement("Select * from cable where id="+id+";");
            rs1 = pst1.executeQuery();
            
            if (!rs1.next()) throw new RuntimeException("Cable not found");
            
            cable.setId(id);
            cable.setName(rs1.getString("name"));
            cable.setCreationDate(rs1.getDate("creation_date"));
            cable.setModificationDate(rs1.getDate("modification_date"));
            cable.setObjectState(rs1.getInt("object_state"));
            cable.setType(rs1.getString("type"));
            
            List<Wire> wires = new ArrayList<Wire>();
            pst2 = conn.prepareStatement("Select * from wire where cable_id="+id+";");
            rs2 = pst2.executeQuery();
            while(rs2.next()){
                Wire wire = new Wire();
                wire.setId(rs2.getLong("id"));
                wire.setName(rs2.getString("name"));
                wire.setCreationDate(rs2.getDate("creation_date"));
                wire.setModificationDate(rs2.getDate("modification_date"));
                wire.setObjectState(rs2.getInt("object_state"));
                wires.add(wire);
            }
                       
        } catch(Exception e){
            Logger.getLogger(CableProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            throw new RuntimeException(e);
        }
        finally {
            try {
                if (rs1!= null) rs1.close();
                if (rs2!= null) rs2.close();
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (conn != null) conn.close();
                
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }        
        }
        return cable;
    }
    
}
